package com.itheima.ydd.mapper;

import com.itheima.ydd.dto.AllCate;
import com.itheima.ydd.dto.ArticleDto;
import com.itheima.ydd.dto.NewUser;
import com.itheima.ydd.dto.UserDto;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Mapper
public interface IndexMapper {
    //查询广告图advimg_num
    @Select("select count(*) from ydd_advimg")
    int advimg_num();

    //查询广告位advpos_num
    @Select("select count(*) from ydd_advpos")
    int advpos_num();

    //查询文章总数article_num
    @Select("select count(*) from ydd_article")
    int article_num();

    //查询文章分类数cate_num
    @Select("select count(*) from ydd_cate")
    int cate_num();

    //查询标签总数tag_num
    @Select("select count(*) from ydd_tag")
    int tag_num();

    //查询点击率最多的7个文章hot_article;
    @Select("select id,title,author,keywords,click from ydd_article order by click desc limit 7")
    List<ArticleDto> hot_article();

    //查询最新用户注册详情(7位用户)new_user
    @Select("select id,username,sex,icon,address from ydd_user order by id desc limit 7")
    List<NewUser> new_user();

    //查询文件分类各类文件相应个数all_cate
    @Select("SELECT ydd_cate.id,ydd_cate.catename , ca.c num FROM (SELECT cateid,COUNT(*) AS c FROM ydd_article GROUP BY cateid) AS ca ,ydd_cate WHERE ca.cateid = ydd_cate.id;")
    List<AllCate> all_cate();

    //查询用户总数，
    @Select("SELECT sex,COUNT(*) as number FROM ydd_user GROUP BY sex")
    List<HashMap<Integer,Long>> user();
}
